﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Transactions;
using System.Data.SqlClient;

using Global.Class.Library;
using DataTransferObject;
using DataTransferObject.MetaDataList;
using DataAccessLayer;
using DataAccessLayer.MetaDataList;
using DataAccessLayer.MetaDataList.ListAddressAreaDTSTableAdapters;

namespace BusinessLogicLayer.MetaDataList
{
    public class ListAddressAreaBLL : NotifyPropertyChangeObject
    {
        public GlobalEnum.TaskID TaskID { get { return GlobalEnum.TaskID.ListAddressArea; } }

        private UserInformation userOrganization;

        private ListAddressAreaMaster listAddressAreaMaster;

        private BindingList<ListAddressAreaDetail> listAddressAreaDetailList;



        public ListAddressAreaBLL()
        {
            try
            {
                if (GlobalVariables.shouldRestoreProcedure) RestoreProcedure();


                userOrganization = new UserInformation();

                listAddressAreaMaster = new ListAddressAreaMaster();

                this.listAddressAreaDetailList = new BindingList<ListAddressAreaDetail>();

                GlobalDefaultValue.Apply(this);


                this.ListAddressAreaMaster.PropertyChanged += new PropertyChangedEventHandler(ListAddressAreaMaster_PropertyChanged);

                this.ListAddressAreaDetailList.ListChanged += new ListChangedEventHandler(ListAddressAreaDetailList_ListChanged);

            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        private void ListAddressAreaMaster_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            this.SetDirty();
        }

        private void ListAddressAreaDetailList_ListChanged(object sender, ListChangedEventArgs e)
        {
            this.SetDirty();
        }




        #region <Adapter>

        private ListAddressAreaListingTableAdapter listingTableAdapter;
        protected ListAddressAreaListingTableAdapter ListingTableAdapter
        {
            get
            {
                if (listingTableAdapter == null) listingTableAdapter = new ListAddressAreaListingTableAdapter();
                return listingTableAdapter;
            }
        }

        private ListAddressAreaTableAdapter masterTableAdapter;
        protected ListAddressAreaTableAdapter MasterTableAdapter
        {
            get
            {
                if (masterTableAdapter == null) masterTableAdapter = new ListAddressAreaTableAdapter();
                return masterTableAdapter;
            }
        }

        private ListAddressAreaDetailTableAdapter detailTableAdapter;
        protected ListAddressAreaDetailTableAdapter DetailTableAdapter
        {
            get
            {
                if (detailTableAdapter == null) detailTableAdapter = new ListAddressAreaDetailTableAdapter();
                return detailTableAdapter;
            }
        }


        #endregion <Adapter>

        #region <Storage>

        public UserInformation UserOrganization
        {
            get { return this.userOrganization; }
            set { this.userOrganization = value; }
        }

        public ListAddressAreaDTS.ListAddressAreaListingDataTable ListAddressAreaListing()
        {
            return this.ListingTableAdapter.GetData();
        }

        public ListAddressAreaMaster ListAddressAreaMaster
        {
            get { return this.listAddressAreaMaster; }
        }

        public BindingList<ListAddressAreaDetail> ListAddressAreaDetailList
        {
            get { return this.listAddressAreaDetailList; }
        }


        #endregion <Storage>

        #region Properties

        #region <Primary Key>

        public int AddressAreaID   //Primary Key
        {
            get { return this.ListAddressAreaMaster.AddressAreaID; }
            private set
            {
                if (this.ListAddressAreaMaster.AddressAreaID != value)
                {
                    this.StopTracking();

                    this.ListAddressAreaMaster.AddressAreaID = value;

                    this.ListAddressAreaGetMaster();
                    this.ListAddressAreaGetDetail();

                    this.StartTracking();
                    this.Reset();
                }

            }
        }

        #endregion <Primary Key>

        private void ListAddressAreaGetMaster()
        {
            if (this.AddressAreaID > 0)
            {
                ListAddressAreaDTS.ListAddressAreaDataTable masterDataTable = this.MasterTableAdapter.GetData(this.AddressAreaID);

                if (masterDataTable.Count > 0)
                {
                    this.ListAddressAreaMaster.StopTracking();

                    this.ListAddressAreaMaster.EntryDate = masterDataTable[0].EntryDate;

                    this.ListAddressAreaMaster.AncestorID = masterDataTable[0].AncestorID;
                    this.ListAddressAreaMaster.ProvinceID = masterDataTable[0].ProvinceID;
                    this.ListAddressAreaMaster.AdministratorLevelID = masterDataTable[0].AdministratorLevelID;

                    this.ListAddressAreaMaster.AreaCode = masterDataTable[0].AreaCode;
                    this.ListAddressAreaMaster.Description = masterDataTable[0].Description;
                    this.ListAddressAreaMaster.Other = masterDataTable[0].Other;

                    this.ListAddressAreaMaster.Leafest = masterDataTable[0].Leafest;

                    this.ListAddressAreaMaster.StartTracking();

                    this.ListAddressAreaMaster.Reset();

                    this.UserOrganization.UserID = masterDataTable[0].UserID;
                    this.UserOrganization.UserOrganizationID = masterDataTable[0].UserOrganizationID;
                }
                else throw new System.ArgumentException("Insufficient get data");
            }
            else
            {
                GlobalDefaultValue.Apply(this.ListAddressAreaMaster);
                this.ListAddressAreaMaster.EntryDate = DateTime.Today;
                this.ListAddressAreaMaster.Reset();
            }
        }


        private void ListAddressAreaGetDetail()
        {
            this.listAddressAreaDetailList.RaiseListChangedEvents = false;
            this.listAddressAreaDetailList.Clear();
            if (this.AddressAreaID >= 0)
            {

                ListAddressAreaDTS.ListAddressAreaDetailDataTable detailDataTable = this.DetailTableAdapter.GetData(this.AddressAreaID);

                if (detailDataTable.Count > 0)
                {
                    foreach (ListAddressAreaDTS.ListAddressAreaDetailRow detailRow in detailDataTable.Rows)
                    {
                        this.listAddressAreaDetailList.Add(new ListAddressAreaDetail(detailRow.CommonID, detailRow.CommonValue, detailRow.Remarks));
                    }
                }
            }
            this.listAddressAreaDetailList.RaiseListChangedEvents = true;
            this.listAddressAreaDetailList.ResetBindings();
        }

        #endregion Properties





        #region Object Permission

        public override bool IsValid
        {
            get
            {
                List<ListAddressAreaDetail> inValidListAddressAreaDetail = this.ListAddressAreaDetailList.Where(listAddressAreaDetail => !listAddressAreaDetail.IsValid).ToList();
                return this.ListAddressAreaMaster.IsValid && inValidListAddressAreaDetail.Count == 0;
            }
        }

        public bool ReadOnly
        {
            get
            {
                try
                {
                    return GlobalUserPermission.GetUserReadOnly(GlobalVariables.GlobalUserInformation.UserID, this.TaskID);
                }
                catch
                {
                    return true;
                }
            }
        }

        public bool Editable
        {
            get
            {
                try
                {
                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, this.TaskID, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("ListAddressAreaEditable", this.AddressAreaID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Verifiable
        {
            get
            {
                try
                {
                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.ListAddressAreaVerifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("ListAddressAreaEditable", this.AddressAreaID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Unverifiable
        {
            get
            {
                try
                {
                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.ListAddressAreaUnverifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("ListAddressAreaEditable", this.AddressAreaID);
                }
                catch
                {
                    return false;
                }
            }
        }


        #endregion Object Permission






        public void Fill(int customerTypeID)
        {
            if (this.AddressAreaID == customerTypeID) this.AddressAreaID = -1; //Enforce to reload
            this.AddressAreaID = customerTypeID;
        }

        public void New()
        {
            if (this.AddressAreaID == 0) this.AddressAreaID = -1;
            this.AddressAreaID = 0;
        }

        public void Edit()
        {

        }

        #region Save & Delete Method


        public bool Save()
        {
            int addressAreaID = 0;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient save", "Save validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (this.ReadOnly) throw new System.ArgumentException("Insufficient save", "Uneditable");

                    if (!this.SaveMaster(ref addressAreaID)) throw new System.ArgumentException("Insufficient save", "Save master");

                    if (!this.SaveDetail(addressAreaID)) throw new System.ArgumentException("Insufficient save", "Save detail");

                    transactionScope.Complete();
                }

                SQLDatabase.ExecuteNonQuery("ListAddressAreaUpdateHierarchyLevel", CommandType.StoredProcedure, "AncestorID", this.listAddressAreaMaster.AncestorID);

                this.Fill(addressAreaID);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        public bool Delete()
        {
            if (this.listAddressAreaMaster.AddressAreaID <= 0) return false;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient delete", "Delete validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient delete", "Uneditable");

                    if (!this.SaveUndo(this.listAddressAreaMaster.AddressAreaID)) throw new System.ArgumentException("Insufficient delete", "Delete detail");

                    if (this.MasterTableAdapter.Delete(this.listAddressAreaMaster.AddressAreaID) != 1) throw new System.ArgumentException("Insufficient delete", "Delete master");

                    transactionScope.Complete();
                }
                this.Fill(0);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        private bool SaveValidate()
        {
            ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

            this.UserOrganization = GlobalUserPermission.GetUserInformation(this.UserOrganization.UserID, this.ListAddressAreaMaster.EntryDate);

            if (this.UserOrganization.UserID <= 0 || this.UserOrganization.UserOrganizationID <= 0) exceptionTable.AddException(new string[] { GlobalVariables.stringFieldRequired, "User information" });

            if (exceptionTable.Table.Rows.Count <= 0 && this.IsValid) return true; else throw new CustomException("Save validate", exceptionTable.Table);
        }


        private bool SaveMaster(ref int addressAreaID)
        {
            ListAddressAreaDTS.ListAddressAreaDataTable masterDataTable;
            ListAddressAreaDTS.ListAddressAreaRow masterRow;

            if (this.listAddressAreaMaster.AddressAreaID <= 0) //Add
            {
                masterDataTable = new ListAddressAreaDTS.ListAddressAreaDataTable();
                masterRow = masterDataTable.NewListAddressAreaRow();
            }
            else //Edit
            {
                if (!this.SaveUndo(listAddressAreaMaster.AddressAreaID)) throw new System.ArgumentException("Insufficient save", "Save undo");
                masterDataTable = this.MasterTableAdapter.GetData(listAddressAreaMaster.AddressAreaID);
                if (masterDataTable.Count > 0) masterRow = masterDataTable[0]; else throw new System.ArgumentException("Insufficient save", "Get for edit");
            }

            masterRow.EntryDate = DateTime.Now;

            masterRow.AncestorID = this.listAddressAreaMaster.AncestorID;
            masterRow.HierarchyLevel = 0;
            masterRow.HierarchyOrder = "";
            masterRow.ProvinceID = this.listAddressAreaMaster.ProvinceID;
            masterRow.AdministratorLevelID = this.listAddressAreaMaster.AdministratorLevelID;

            masterRow.AreaCode = this.listAddressAreaMaster.AreaCode;
            masterRow.Description = this.listAddressAreaMaster.Description;
            masterRow.Other = this.listAddressAreaMaster.Other;

            masterRow.Leafest = false;

            masterRow.AncestorID = this.listAddressAreaMaster.AncestorID;

            masterRow.UserID = this.UserOrganization.UserID;
            masterRow.UserOrganizationID = this.UserOrganization.UserOrganizationID;

            if (this.listAddressAreaMaster.AddressAreaID <= 0) masterDataTable.AddListAddressAreaRow(masterRow);

            int rowsAffected = this.MasterTableAdapter.Update(masterRow);

            addressAreaID = masterRow.AddressAreaID;

            return rowsAffected == 1;

        }


        private bool SaveDetail(int addressAreaID)
        {
            int serialID = 0; int rowsAffected = 0;


            #region <Save collection>

            serialID = 0;

            ListAddressAreaDTS.ListAddressAreaDetailDataTable detailDataTable = new ListAddressAreaDTS.ListAddressAreaDetailDataTable();

            foreach (ListAddressAreaDetail listAddressAreaDetail in this.ListAddressAreaDetailList)
            {
                ListAddressAreaDTS.ListAddressAreaDetailRow detailRow = detailDataTable.NewListAddressAreaDetailRow();

                detailRow.AddressAreaID = addressAreaID;
                detailRow.SerialID = ++serialID;

                detailRow.CommonID = listAddressAreaDetail.CommonID;
                detailRow.CommonValue = listAddressAreaDetail.CommonValue;

                detailRow.Remarks = listAddressAreaDetail.Remarks;

                detailDataTable.AddListAddressAreaDetailRow(detailRow);
            }

            rowsAffected = this.DetailTableAdapter.Update(detailDataTable);
            if (rowsAffected != this.listAddressAreaDetailList.Count) throw new System.ArgumentException("Insufficient save", "Save detail");


            #endregion <Save collection>


            return true;
        }


        private bool SaveUndo(int addressAreaID)
        {
            this.DetailTableAdapter.Delete(addressAreaID);

            return true;
        }



        #endregion



        private void RestoreProcedure()
        {
            string queryString;
            string[] queryArray;



            queryString = "     " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      AddressAreaID, Description, AncestorID, Other, EntryDate " + "\r\n";
            queryString = queryString + "       FROM        ListAddressArea " + "\r\n";
            queryString = queryString + "       ORDER BY    Description " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListAddressAreaListing", queryString);



            queryString = "     @AddressAreaID Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      AddressAreaID, Description, AncestorID, HierarchyLevel, HierarchyOrder, AreaCode, Leafest, ProvinceID, AdministratorLevelID, Other, EntryDate, UserID, UserOrganizationID " + "\r\n";
            queryString = queryString + "       FROM        ListAddressArea " + "\r\n";
            queryString = queryString + "       WHERE       AddressAreaID = @AddressAreaID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListAddressAreaSelect", queryString);


            queryString = "     @Description nvarchar(100),	@AncestorID int, @HierarchyLevel int,  @HierarchyOrder nvarchar(50), @AreaCode nvarchar(100), @Leafest bit, @ProvinceID int, @AdministratorLevelID int, @Other nvarchar(100), @EntryDate datetime, @UserID int, @UserOrganizationID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO ListAddressArea (Description, AncestorID, HierarchyLevel, HierarchyOrder, AreaCode, Leafest, ProvinceID, AdministratorLevelID, Other, EntryDate, UserID, UserOrganizationID) VALUES (@Description, @AncestorID, @HierarchyLevel, @HierarchyOrder, @AreaCode, @Leafest, @ProvinceID, @AdministratorLevelID, @Other, @EntryDate, @UserID, @UserOrganizationID) " + "\r\n";
            queryString = queryString + "       SELECT      AddressAreaID, Description, AncestorID, HierarchyLevel, HierarchyOrder, AreaCode, Leafest, ProvinceID, AdministratorLevelID, Other, EntryDate, UserID, UserOrganizationID FROM ListAddressArea WHERE AddressAreaID = SCOPE_IDENTITY() " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListAddressAreaInsert", queryString);


            queryString = "     @AddressAreaID int, @Description nvarchar(100), @AncestorID int, @HierarchyLevel int, @HierarchyOrder nvarchar(50), @AreaCode nvarchar(100), @Leafest bit, @ProvinceID int, @AdministratorLevelID int, @Other nvarchar(100), @EntryDate datetime, @UserID int, @UserOrganizationID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      ListAddressArea SET Description = @Description, AncestorID = @AncestorID, HierarchyLevel = @HierarchyLevel, HierarchyOrder = @HierarchyOrder, AreaCode = @AreaCode, Leafest = @Leafest, ProvinceID = @ProvinceID, AdministratorLevelID = @AdministratorLevelID, Other = @Other, EntryDate = @EntryDate, UserID = @UserID, UserOrganizationID = @UserOrganizationID WHERE AddressAreaID = @AddressAreaID " + "\r\n";
            queryString = queryString + "       SELECT      AddressAreaID, Description, AncestorID, HierarchyLevel, HierarchyOrder, AdministratorLevelID, Other, EntryDate, UserID, UserOrganizationID FROM ListAddressArea WHERE AddressAreaID = @AddressAreaID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListAddressAreaUpdate", queryString);


            queryString = " @AddressAreaID int ";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM ListAddressArea WHERE AddressAreaID = @AddressAreaID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("ListAddressAreaDelete", queryString);






            queryString = " @AddressAreaID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      AddressAreaID, SerialID, CommonID, CommonValue, Remarks " + "\r\n";
            queryString = queryString + "       FROM        ListAddressAreaDetail " + "\r\n";
            queryString = queryString + "       WHERE       AddressAreaID = @AddressAreaID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListAddressAreaDetailSelect", queryString);


            queryString = " @AddressAreaID int, @SerialID int,	@CommonID int, @CommonValue float, @Remarks nvarchar(100) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO ListAddressAreaDetail (AddressAreaID, SerialID, CommonID, CommonValue, Remarks) VALUES (@AddressAreaID, @SerialID, @CommonID, @CommonValue, @Remarks) " + "\r\n";
            queryString = queryString + "       SELECT      AddressAreaID, SerialID, CommonID, CommonValue, Remarks FROM ListAddressAreaDetail WHERE (AddressAreaID = @AddressAreaID) AND (SerialID = @SerialID) " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListAddressAreaDetailInsert", queryString);



            queryString = " @AddressAreaID int, @SerialID int,	@CommonID int, @CommonValue float, @Remarks nvarchar(100) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      ListAddressAreaDetail SET CommonID = @CommonID, CommonValue = @CommonValue, Remarks = @Remarks WHERE AddressAreaID = @AddressAreaID AND SerialID = @SerialID " + "\r\n";
            queryString = queryString + "       SELECT      AddressAreaID, SerialID, CommonID, CommonValue, Remarks FROM ListAddressAreaDetail WHERE AddressAreaID = @AddressAreaID AND SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListAddressAreaDetailUpdate", queryString);



            queryString = " @AddressAreaID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM ListAddressAreaDetail WHERE AddressAreaID = @AddressAreaID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("ListAddressAreaDetailDelete", queryString);



            queryString = " @AncestorID Int ";
            queryString = queryString + " WITH ENCRYPTION ";
            queryString = queryString + " AS ";

            queryString = queryString + "   DECLARE @AncestorHierarchyOrder NVarchar(30), @AncestorHierarchyLevel Int  ";
            queryString = queryString + "   DECLARE @Cursor_Rows Int, @SerialNo Int ";

            queryString = queryString + "   SET @AncestorHierarchyOrder = N'' ";
            queryString = queryString + "   SET @AncestorHierarchyLevel = 0 ";
            queryString = queryString + "   SET @SerialNo = 0 ";
            queryString = queryString + "   SET @Cursor_Rows = 0 ";

            queryString = queryString + "   IF @AncestorID <> 0 ";
            queryString = queryString + "   BEGIN ";

            queryString = queryString + "       DECLARE Ancestor_Cursor CURSOR LOCAL FOR ";
            queryString = queryString + "       SELECT TOP 1 HierarchyOrder, HierarchyLevel FROM ListAddressArea ";
            queryString = queryString + "       WHERE AddressAreaID = @AncestorID ";

            queryString = queryString + "       OPEN Ancestor_Cursor ";

            queryString = queryString + "       FETCH NEXT FROM Ancestor_Cursor INTO @AncestorHierarchyOrder, @AncestorHierarchyLevel ";

            queryString = queryString + "       CLOSE Ancestor_Cursor ";
            queryString = queryString + "       DEALLOCATE Ancestor_Cursor ";


            queryString = queryString + "   END ";

            queryString = queryString + "   DECLARE @AddressAreaID Int ";

            queryString = queryString + "   DECLARE AddressArea_Cursor CURSOR LOCAL SCROLL FOR ";
            queryString = queryString + "   SELECT AddressAreaID FROM ListAddressArea ";
            queryString = queryString + "   WHERE AncestorID = @AncestorID ORDER BY Description ";
            queryString = queryString + "   FOR UPDATE ";

            queryString = queryString + "   OPEN AddressArea_Cursor ";

            queryString = queryString + "   FETCH LAST FROM AddressArea_Cursor INTO @AddressAreaID ";
            queryString = queryString + "   IF @@FETCH_STATUS = 0 ";
            queryString = queryString + "   BEGIN ";
            queryString = queryString + "       SET @Cursor_Rows = @@CURSOR_ROWS ";
            queryString = queryString + "       FETCH FIRST FROM AddressArea_Cursor INTO @AddressAreaID ";
            queryString = queryString + "   END ";

            queryString = queryString + "   WHILE @@FETCH_STATUS = 0";
            queryString = queryString + "   BEGIN";

            queryString = queryString + "       SET @SerialNo = @SerialNo + 1 ";

            queryString = queryString + "       UPDATE ListAddressArea SET HierarchyOrder = @AncestorHierarchyOrder +  REPLICATE('0', DATALENGTH(CONVERT (NVarchar(30), @Cursor_Rows)) - DATALENGTH(CONVERT (NVarchar(30), @SerialNo))) + CONVERT (NVarchar(30), @SerialNo), HierarchyLevel = @AncestorHierarchyLevel + 1 ";
            queryString = queryString + "       WHERE CURRENT OF AddressArea_Cursor ";

            queryString = queryString + "       EXECUTE ListAddressAreaUpdateHierarchyLevel @AncestorID = @AddressAreaID ";

            queryString = queryString + "       FETCH NEXT FROM AddressArea_Cursor INTO @AddressAreaID ";

            queryString = queryString + "   END ";

            queryString = queryString + "   CLOSE AddressArea_Cursor ";
            queryString = queryString + "   DEALLOCATE AddressArea_Cursor ";

            SQLDatabase.CreateStoredProcedure("ListAddressAreaUpdateHierarchyLevel", queryString);






            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[5];
            queryArray[0] = "   SELECT      TOP 1 AddressAreaID FROM ListAddressArea WHERE AncestorID = @FindIdentityID ";
            queryArray[1] = "   SELECT      TOP 1 AddressAreaID FROM ListSalesmenAddressArea WHERE AddressAreaID = @FindIdentityID ";
            queryArray[2] = "   SELECT      TOP 1 AddressAreaID FROM ListDistributorName WHERE AddressAreaID = @FindIdentityID ";
            queryArray[3] = "   SELECT      TOP 1 AddressAreaID FROM ListCustomerName WHERE AddressAreaID = @FindIdentityID ";
            queryArray[4] = "   SELECT      TOP 1 InMarketID FROM InMarketMaster WHERE CustomerAddressAreaID = @FindIdentityID OR DistributorAddressAreaID = @FindIdentityID OR SalesmenAddressAreaID = @FindIdentityID ";


            SQLDatabase.CreateProcedureToCheckExisting("ListAddressAreaEditable", queryArray);




        }
    }
}
